package com.sunda.spmsoversea.mapper;

import com.sunda.spmsoversea.entity.OverseaRequisitionDtl;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 领用申请表明细 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-09-22
 */
@Mapper
public interface OverseaRequisitionDtlMapper extends BaseMapper<OverseaRequisitionDtl> {

    @Select("SELECT ORD.AUTO_ID_REQUISITION_DTL AS \"autoIdRequisitionDtl\",\n" +
            "       ORD.UUID_REQUISITION AS \"uuidRequisition\",\n" +
            "       ORD.ITEM AS \"item\",\n" +
            "       ORD.WERKS AS \"werks\",\n" +
            "       ORD.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "       ORD.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       ORD.MATERIAL_NO AS \"materialNo\",\n" +
            "       ORD.APPLY_QTY_BASIC_UNIT AS \"applyQtyBasicUnit\",\n" +
            "       ORD.ACTUAL_QTY_BASIC_UNIT AS \"actualQtyBasicUnit\",\n" +
            "       ORD.BASIC_UNIT AS \"basicUnit\",\n" +
            "       ORD.APPLY_QTY_UNIT_SALES AS \"applyQtyUnitSales\",\n" +
            "       ORD.ACTUAL_QTY_UNIT_SALES AS \"actualQtyUnitSales\",\n" +
            "       ORD.UNIT_SALES AS \"unitSales\",\n" +
            "       ORD.REMARKS AS \"remarks\", \n" +
            "       ORD.MOVEMENT_TYPE AS \"movementType\", \n" +
            "       ORD.ACTUAL_MOVEMENT_TYPE AS \"actualMovementType\", \n" +
            "       SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\", \n" +
            "       SAP.MATERIAL_EN_DESC AS \"materialEnDesc\", \n" +
            "       SAP.NUMERATOR AS \"numerator\", \n" +
            "       SAP.DENOMINATOR AS \"denominator\"\n" +
            "FROM OVERSEA_REQUISITION_DTL ORD\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON ORD.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "WHERE UUID_REQUISITION = #{uuidRequisition} ORDER BY ORD.ITEM ASC ")
    List<Map<String, Object>> getRequisitionDtlList(@Param("uuidRequisition") String uuidRequisition);

    @Select("SELECT RE.*\n" +
            "FROM (\n" +
            "    SELECT ORD.AUTO_ID_REQUISITION_DTL AS \"autoIdRequisitionDtl\",\n" +
            "           ORD.UUID_REQUISITION AS \"uuidRequisition\",\n" +
            "           ORD.ITEM AS \"item\",\n" +
            "           ORD.WERKS AS \"werks\",\n" +
            "           ORD.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "           ORD.WHS_LOCATION_CODE AS \"whsLocationCode\"\n" +
            "    FROM OVERSEA_REQUISITION_DTL ORD\n" +
            "    WHERE UUID_REQUISITION = #{uuidRequisition} ORDER BY ORD.ITEM DESC\n" +
            "    ) RE\n" +
            "WHERE ROWNUM = 1 ")
    OverseaRequisitionDtl getMaxItem(@Param("uuidRequisition") String uuidRequisition);

}
